package com.javafx.everythingdemo.config;

import org.apache.commons.lang.StringEscapeUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SQLiteConfig {

    public static final Connection c;

    static {
        try {
            c = DriverManager.getConnection("jdbc:sqlite:my_everything.db");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void main( String args[] ) {

//        ArrayList<Integer> list = new ArrayList<>();
//        delect(list);
//        updateID(1,"xxx","123");
//        selectAll();
        deletData();
    }


    /**
     * 查询所有
     */
    public static Map<String,String> selectAll(){
        String sql=null;
        Map<String,String> map=new HashMap<>();
        try {
            sql="select * from everything";
            PreparedStatement pstmt = c.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()){
                System.out.println(
                        "id="+rs.getInt("id")+"\t"+
                        "id="+rs.getString("NAME")+"\t"+
                        "url="+rs.getString("url")+"\t"+
                        "type="+rs.getString("type")
                );
                map.put(rs.getString("NAME"), rs.getString("url"));
            }
        }catch (Exception e){
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        return map;
    }

    /**
     * 按名字查找
     * @param name
     * @return
     */
    public static Map<String,String> selectName(String name){
        String sql=null;
        Map<String,String> map=new HashMap<>();
        try {
            sql="select * from everything where NAME=? limit 3";
            PreparedStatement pstmt = c.prepareStatement(sql);
            pstmt.setString(1,name);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()){
                map.put(rs.getString("NAME"), rs.getString("url"));
            }
        }catch (Exception e){
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        return map;
    }

    /**
     * 更新数据
     */

    public static void update(String url,String type,String urlk,String typek){
        try {
            String sql=null;
            sql="update everything set url=?,type=? where url=? and type=?";
            PreparedStatement ptmst = c.prepareStatement(sql);
            ptmst.setString(1,urlk);
            ptmst.setString(2,typek);
            ptmst.setString(3,url);
            ptmst.setString(4,type);
            ptmst.executeUpdate();

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    public static void updateID(int id,String urlk,String typek){
        try {
            String sql=null;
            sql="update everything set url=?,type=? where ID=?";
            PreparedStatement ptmst = c.prepareStatement(sql);
            ptmst.setString(1,urlk);
            ptmst.setString(2,typek);
            ptmst.setInt(3,id);
            ptmst.executeUpdate();

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 删除
     */
    public static void delect(List<Integer> list){
        try {
            String sql=null;
            sql = "Delete from everything where ID=?";
            PreparedStatement pstmt=c.prepareStatement(sql);
            for (Integer integer : list) {
                pstmt.setInt(1,integer);
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        } catch ( Exception e ) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
    }

    public static void deletData(){
        try {
            Statement statement = c.createStatement();
            statement.executeUpdate("DELETE FROM everything;");
            System.out.println("已删除");
        }catch (Exception e){
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
    }



    /**
     * 插入
     * @param list
     */
    public static void insert(List<Map<String,String>> list){
        Map<String,String> filesmap=list.get(0);//文件
        Map<String,String> foldermap=list.get(1);//文件夹
        try {
            String sql=null;
            int i=1;
            sql = "INSERT INTO everything (ID,NAME,url,type) VALUES (?,?,?,?)";
            PreparedStatement pstmt=c.prepareStatement(sql);
//            插入文件数据
            for (String str:filesmap.keySet()) {
                String name = StringEscapeUtils.escapeSql(str);
                String url1 = StringEscapeUtils.escapeSql(filesmap.get(str));
                    pstmt.setInt(1,i);
                    pstmt.setString(2,name);
                    pstmt.setString(3,url1);
                    pstmt.setString(4,"文件");
                    pstmt.addBatch();
                i++;
            }
//            插入文件夹数据
            for (String str:foldermap.keySet()) {
                String name = StringEscapeUtils.escapeSql(str);
                String url1 = StringEscapeUtils.escapeSql(foldermap.get(str));
                pstmt.setInt(1,i);
                pstmt.setString(2,name);
                pstmt.setString(3,url1);
                pstmt.setString(4,"文件夹");
                pstmt.addBatch();
                System.out.println(i);
                i++;
            }
            pstmt.executeBatch();

        } catch ( Exception e ) {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            System.exit(0);
        }
        System.out.println("Records created successfully");
    }

//    public static void insert(){
//        Connection c = null;
//        Statement stmt = null;
//        try {
//            c = DriverManager.getConnection("jdbc:sqlite:my_everything.db");
//            c.setAutoCommit(false);
//            stmt = c.createStatement();
//            String sql = "INSERT INTO everything (ID,NAME,url,type) VALUES (3, 'Paul','111','California')";
//            System.out.println(222);
//            stmt.executeUpdate(sql);
//            System.out.println(111);
//            stmt.close();
//            c.commit();
//            c.close();
//        } catch ( Exception e ) {
//            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
//            System.exit(0);
//        }
//        System.out.println("Records created successfully");
//    }

    /**
     * 创建数据库，创建表
     */
    public static void createTabular(){
        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:my_everything.db");
            System.out.println("Opened database successfully");
            stmt = c.createStatement();
            String sql = "CREATE TABLE everything " +
                    "(ID INT PRIMARY KEY     NOT NULL," +
                    " NAME           TEXT    NOT NULL, " +
                    " url           TEXT    NOT NULL, " +
                    " type           TEXT    NOT NULL)" ;
            stmt.executeUpdate(sql);
            stmt.close();
            c.close();
        } catch ( Exception e ) {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            System.exit(0);
        }
        System.out.println("Table created successfully");
    }
}
